import tkinter
import tkinter.filedialog
import os
import tkinter.ttk
from openpyxl  import load_workbook
import json

# 用例模板转化
class CreateExcel():
    def __init__(self,tk_mainwindow) -> None:
        self.tk_mainwindow = tk_mainwindow
        self.rownum = 0
        self.horizontal = 600
        self.vertical = 500
        
        
        # self.config = {"sheet1":"sheet1","sheet2":"sheet1","fundname":"信托TA6","custname":"国投","username":"xxx666","mapping":{"C":"C","D":"N","F":"B","H":"I","G":"J","I":"K"}}
        self.config = self.load_config()
        self.file_path = tkinter.StringVar()
        self.file_path_new = tkinter.StringVar()
        self.dir_path = tkinter.StringVar()
        self.file_path_sheet = tkinter.StringVar(value=self.config["sheet1"])
        self.file_path_new_sheet = tkinter.StringVar(value=self.config["sheet2"])
        self.fundname = tkinter.StringVar(value=self.config["fundname"])
        self.custname = tkinter.StringVar(value=self.config["custname"])
        self.username = tkinter.StringVar(value=self.config["username"])
        self.tips = tkinter.StringVar(value='注意！原模板需要从效能平台导出')

        self.showWidget()
        
    def showWidget(self):
        self.tk_mainwindow.geometry(f'{self.horizontal}x{self.vertical}')

        this_rownum = self.rownumUper()
        tkinter.Label(self.tk_mainwindow,text='原模板路径').grid(row=this_rownum,column=0)
        tkinter.Entry(self.tk_mainwindow,textvariable = self.file_path,state='readonly',width=50).grid(row=this_rownum,column=1,sticky=tkinter.W+tkinter.E,columnspan = 5)
        tkinter.Button(self.tk_mainwindow,text='选择',command = self.select_file_path).grid(row=this_rownum,column=10)

        this_rownum = self.rownumUper()
        tkinter.Label(self.tk_mainwindow,text='原模板sheet页名称').grid(row=this_rownum,column=0)
        tkinter.Entry(self.tk_mainwindow,textvariable = self.file_path_sheet).grid(row=this_rownum,column=1,sticky='W',columnspan = 5)

        this_rownum = self.rownumUper()
        tkinter.Label(self.tk_mainwindow,text='新模板路径').grid(row=this_rownum,column=0)
        tkinter.Entry(self.tk_mainwindow,textvariable = self.file_path_new,state='readonly',width=50).grid(row=this_rownum,column=1,sticky=tkinter.W+tkinter.E,columnspan = 5)
        tkinter.Button(self.tk_mainwindow,text='选择',command = self.select_file_path_new).grid(row=this_rownum,column=10)

        this_rownum = self.rownumUper()
        tkinter.Label(self.tk_mainwindow,text='新模板sheet页名称').grid(row=this_rownum,column=0)
        tkinter.Entry(self.tk_mainwindow,textvariable = self.file_path_new_sheet).grid(row=this_rownum,column=1,sticky='W',columnspan = 5)

        this_rownum = self.rownumUper()
        tkinter.Label(self.tk_mainwindow,text='生成路径').grid(row=this_rownum,column=0)
        tkinter.Entry(self.tk_mainwindow,textvariable = self.dir_path,state='readonly',width=50).grid(row=this_rownum,column=1,sticky=tkinter.W+tkinter.E,columnspan = 5)
        tkinter.Button(self.tk_mainwindow,text='选择',command = self.select_dir_path).grid(row=this_rownum,column=10)
        tkinter.Button(self.tk_mainwindow,text='打开目标路径',command = self.open_path).grid(row=this_rownum,column=11)

        """
        # 分割线
        this_rownum = self.rownumUper()
        tkinter.ttk.Separator(self.tk_mainwindow, orient=tkinter.HORIZONTAL).grid(row=this_rownum,column=0,columnspan = 20,sticky='EW',pady=5)

        this_rownum = self.rownumUper()
        tkinter.Label(self.tk_mainwindow,text='映射配置').grid(row=this_rownum,column=0)
        
        com = tkinter.ttk.Combobox(self.tk_mainwindow,textvariable = tkinter.StringVar(value='映射配置'))
        com.grid(row=this_rownum,column=1,sticky='W',columnspan = 5)
        com["value"] = ("默认")
        com.current(0)

        def xFunc(event):
            print(com.get())

        com.bind("<<ComboboxSelected>>",xFunc)


        this_rownum = self.rownumUper()
        tkinter.Label(self.tk_mainwindow,text='配置详情').grid(row=this_rownum,column=0,rowspan=2,sticky=tkinter.N+tkinter.S)
        tkinter.Label(self.tk_mainwindow,text='原模板列').grid(row=this_rownum,column=1,sticky='E')
        tkinter.Label(self.tk_mainwindow,text='->').grid(row=this_rownum,column=2,sticky='WE')
        tkinter.Label(self.tk_mainwindow,text='新模板').grid(row=this_rownum,column=3,sticky='W')

        this_rownum = self.rownumUper()
        tkinter.Label(self.tk_mainwindow,text='A',width=5).grid(row=this_rownum,column=1,sticky='E')
        tkinter.Label(self.tk_mainwindow,text='->',width=5).grid(row=this_rownum,column=2,sticky='WE')
        tkinter.Label(self.tk_mainwindow,text='B',width=5).grid(row=this_rownum,column=3,sticky='W')

        # 分割线
        this_rownum = self.rownumUper()
        tkinter.ttk.Separator(self.tk_mainwindow, orient=tkinter.HORIZONTAL).grid(row=this_rownum,column=0,columnspan = 20,sticky='EW',pady=5)
        """

        this_rownum = self.rownumUper()
        tkinter.Label(self.tk_mainwindow,text='产品').grid(row=this_rownum,column=0)
        tkinter.Entry(self.tk_mainwindow,textvariable = self.fundname).grid(row=this_rownum,column=1,sticky=tkinter.W,columnspan = 5)

        this_rownum = self.rownumUper()
        tkinter.Label(self.tk_mainwindow,text='客户').grid(row=this_rownum,column=0)
        tkinter.Entry(self.tk_mainwindow,textvariable = self.custname).grid(row=this_rownum,column=1,sticky=tkinter.W,columnspan = 5)

        this_rownum = self.rownumUper()
        tkinter.Label(self.tk_mainwindow,text='域账号').grid(row=this_rownum,column=0)
        tkinter.Entry(self.tk_mainwindow,textvariable = self.username).grid(row=this_rownum,column=1,sticky=tkinter.W,columnspan = 5)

        this_rownum = self.rownumUper()
        tkinter.Button(self.tk_mainwindow,text='生成EXCEL',bg="lightblue",command = self.dealwith_excel).grid(row=this_rownum,column=0)
        tkinter.Label(self.tk_mainwindow,textvariable=self.tips,fg='red').grid(row=this_rownum,column=1,sticky=tkinter.W+tkinter.E,columnspan = 5)

    def load_config(self):
        with open("configs\\templateMap.json",'r') as file:
            return json.load(file)

    def save_config(self):
        with open("configs\\templateMap.json",'w') as file:
            json.dump(self.config, file, indent=4,ensure_ascii = False)
                    
    def rownumUper(self):
        realnum = self.rownum
        self.rownum += 1
        return realnum


    def select_file_path(self):
        path_ = tkinter.filedialog.askopenfilename()
        if path_ == '':
            self.file_path.get()
        else:
            path_ = path_.replace("/","\\")
            self.file_path.set(path_)

    def select_file_path_new(self):
        path_ = tkinter.filedialog.askopenfilename()
        if path_ == '':
            self.file_path_new.get()
        else:
            path_ = path_.replace("/","\\")
            dir_path_ = path_.replace('\\'+str(path_).split('\\')[-1],"")
            self.file_path_new.set(path_)
            self.dir_path.set(dir_path_)

    def select_dir_path(self):
        path_ = tkinter.filedialog.askdirectory()
        if path_ == '':
            self.dir_path.get()
        else:
            path_ = path_.replace("/","\\")
            self.dir_path.set(path_)
            

    def open_path(self):
        dir = os.path.dirname(self.dir_path.get()+'\\')
        os.system('start ' + dir)
        
    # 处理excel模板转化逻辑
    def dealwith_excel(self):
        self.save_config()
        workbook = load_workbook(self.file_path.get())
        sheet = workbook[self.file_path_sheet.get()]

        # 取值的行
        row_num = 3
        # 写值的行
        current_rownum = 3

        # 新文件行数
        count_num = 0
        # 用例数
        case_num = 0

        test_point = ''
        need_no = ''


        while True:
            # 新模板
            workbook2 = load_workbook(self.file_path_new.get())
            sheet_w = workbook2[self.file_path_new_sheet.get()]

            # 本行标记
            cell_no_B = 'B'+str(row_num)
            cell_no_C = 'C'+str(row_num)
            cell_no_N = 'N'+str(row_num)
            cell_no_I = 'I'+str(row_num)
            cell_no_J = 'J'+str(row_num)
            cell_no_K = 'K'+str(row_num) 

            # print(f"检索到第{row_num}行")
            row_num +=1
            
            # 如果本行的I列是空的 说明数据提取完成
            if sheet[cell_no_I].value == None: 
                newtips = f"成功生成{case_num}个用例"
                self.tips.set(newtips)
                break
            

            # 如果B列和I列都有值，说明是新的一行 创建新文件，添加到文件名列表
            if sheet[cell_no_B].value != None and sheet[cell_no_I].value != None:
                # 写值的行
                current_rownum = 3
                # 把当前提取的行写入sheet_w中
                w_A = 'A'+ str(current_rownum)
                w_C = 'C'+ str(current_rownum)
                w_D = 'D'+ str(current_rownum)
                w_F = 'F'+ str(current_rownum)
                w_H = 'H'+ str(current_rownum)
                w_G = 'G'+ str(current_rownum)
                w_I = 'I'+ str(current_rownum)
                # 储存当前行的数据
                sheet_w[w_A] = '功能模块'   #功能点
                sheet_w[w_C] = sheet[cell_no_C].value
                sheet_w[w_D] = sheet[cell_no_N].value
                sheet_w[w_F] = sheet[cell_no_B].value
                sheet_w[w_H] = sheet[cell_no_I].value
                sheet_w[w_G] = sheet[cell_no_J].value
                sheet_w[w_I] = sheet[cell_no_K].value

                test_point = sheet[cell_no_B].value     # 测试点F3
                youxian = sheet[cell_no_C].value        # 优先级C3
                need_no = sheet[cell_no_N].value        # 需求编号D3     

                # 创建文件
                case_num +=1
                
                client_name = self.custname.get()
                user_name = self.username.get()
                excel_name = self.fundname.get() +'-'+client_name+'-' + str(need_no) +'-'+str(test_point)+'-'+user_name +'.xlsx'
                excel_name_path = self.dir_path.get() + "\\" +excel_name
                workbook2.save(excel_name_path)

            # 如果B是空的,I是有值的，说明不是新行，更新在原来的文件里
            if sheet[cell_no_B].value == None and sheet[cell_no_I].value != None:
                workbook3 = load_workbook(excel_name_path)
                sheet_3 = workbook3[self.file_path_new_sheet.get()]

                # 写值的行
                current_rownum += 1
                # 把当前提取的行写入sheet_w中
                w_A = 'A'+ str(current_rownum)
                w_C = 'C'+ str(current_rownum)
                w_D = 'D'+ str(current_rownum)
                w_F = 'F'+ str(current_rownum)
                w_H = 'H'+ str(current_rownum)
                w_G = 'G'+ str(current_rownum)
                w_I = 'I'+ str(current_rownum)

                sheet_3[w_C] = sheet[cell_no_C].value
                sheet_3[w_D] = sheet[cell_no_N].value
                sheet_3[w_F] = sheet[cell_no_B].value
                sheet_3[w_H] = sheet[cell_no_I].value
                sheet_3[w_G] = sheet[cell_no_J].value
                sheet_3[w_I] = sheet[cell_no_K].value

                workbook3.save(excel_name_path)